package org.trc.io;

import static java.sql.ResultSet.TYPE_SCROLL_SENSITIVE;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

import org.trc.simulation.STrajectory;
import org.trc.simulation.STrajectoryPoint;
import org.trc.traffic.AggLinkTraffic;
import org.trc.trajectory.*;

import org.geotools.data.FileDataStore;
import org.geotools.data.FileDataStoreFinder;
import org.geotools.data.shapefile.shp.ShapefileException;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.geometry.MismatchedDimensionException;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.NoSuchAuthorityCodeException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.MathTransform;
import org.opengis.referencing.operation.TransformException;
import org.postgis.jts.JtsGeometry;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.Point;

public class DBAccess extends DBConn{

	//----------------------------------获取车辆轨迹-------------------------------------//
	
	/**
	 * 
	 * 从数据库中获取车辆轨迹Trajectory
	 * 
	 * @param carID
	 * @param startTime
	 * @param endTime
	 * @return
	 */	
	public static Trajectory getTrajectoryByCID(String carID, GregorianCalendar startTime, GregorianCalendar endTime){
		Trajectory traj = new Trajectory(carID, startTime, endTime);
		
		String query= "SELECT * FROM fcdwh_1 " + "WHERE CID = "
		 + "'" + carID + "'" 
		 + " AND UPTIMES >= " 
	     + startTime 
	     + " AND UPTIMES <= " 
	     + endTime 
	     + " ORDER BY UPSECONDS ASC";
		 
		try{
			Connection connection = getPgConn("localhost:5432/FCD", "postgres", "123456");
	        Statement select = connection.createStatement(TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	        ResultSet result = select.executeQuery(query);
	        //SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	        TrajectoryPoint point = null;
	        while(result.next()){
	        	point = new TrajectoryPoint();;
	            point.carID=result.getString("cid");
				//tpSet[i].lat=result.getDouble("latitude");
				//tpSet[i].lon=result.getDouble("longtitude");
	            point.speed=result.getDouble("speed");
	            point.direction=result.getDouble("angle");
	            point.status=result.getInt("status");
//				tpSet[i].timestamp=result.getLong("upseconds");			
	        }
	        result.close();
	        select.close();
	        connection.close();
		 }
		 catch(Exception e) {	        	
	        System.err.println("MySQL Statement Error: " + query);
	        e.printStackTrace();
	     }		 
		 return traj;
	}
	
	/**
	 * 从CSV文件中获取轨迹
	 * @param csvFile
	 * @return
	 * @throws ShapefileException
	 * @throws IOException
	 * @throws TransformException 
	 * @throws MismatchedDimensionException 
	 */
	public static Trajectory getTrajectoryByCSVFile(File csvFile) throws ShapefileException, IOException, MismatchedDimensionException, TransformException {		
		String carID = getFileName(csvFile.toString());
		Trajectory traj = new Trajectory(carID);
			
		try {
			BufferedReader reader = new BufferedReader(new FileReader(csvFile));
			String line = reader.readLine();
			String[] dataEntry = null;
			
		    TrajectoryPoint trajPoint = null;
		    
			int tripID = 0;
			int curStatus = 0;
			int preStatus = 0;
			
			while (line != null) {
				trajPoint = new TrajectoryPoint();
				trajPoint.carID = carID;
				dataEntry = line.split(",");
				
				curStatus = Integer.parseInt(dataEntry[14].trim());
				if( curStatus == 1 && curStatus != preStatus ){ //生成新的TRIPID
					tripID++;
					trajPoint.tripID = String.valueOf(tripID); 						
				}
				else if( curStatus == 1 && curStatus == preStatus ){ //延续已生成TRIPID
					trajPoint.tripID = String.valueOf(tripID);
				} 
				preStatus = curStatus;
				
				// 道路ID
				String linkid = dataEntry[2].trim(); 
	        	if(linkid.substring(0, 2).equals("36"))
	        		linkid = "4554" + linkid.substring(2);
	        	else if(linkid.substring(0, 2).equals("46"))
	        		linkid = "4654" + linkid.substring(2);
	        	trajPoint.linkID = linkid;
				
				double lon = Double.parseDouble(dataEntry[4].trim()); // 经度 
				double lat = Double.parseDouble(dataEntry[5].trim()); // 维度
				Coordinate coordinate = new Coordinate(lon, lat);
				trajPoint.geom = new GeometryFactory().createPoint(coordinate);
				trajPoint.geom = (Point) JTS.transform(trajPoint.geom, getTransformFromEpsgCode("EPSG:4326", "EPSG:32650"));
				//trajPoint.offset = new LengthIndexedLine(road.getEdgeById(trajPoint.linkID).line).indexOf(coordinate);				
				trajPoint.speed =  Double.parseDouble(dataEntry[8].trim())* 3.6; // 速度
				trajPoint.direction = Double.parseDouble(dataEntry[9].trim()); // 方向角
				trajPoint.seconds = Integer.parseInt(dataEntry[13].trim()); // 时间（秒）
				trajPoint.timestamp = new GregorianCalendar(2008, 11, 28, trajPoint.seconds / 3600, trajPoint.seconds % 3600 / 60, trajPoint.seconds % 3600 % 60);//时间
				trajPoint.status = Integer.parseInt(dataEntry[14].trim()); // 状态
				
				traj.addTrajectoryPoint(trajPoint);
				line = reader.readLine();
		  }
			
		} catch (IOException ioe) {
			ioe.printStackTrace();
		} 
		
		return 	traj;	
	}
	
	//----------------------------------获取Linktraffic---------------------------------//
	
	/**
	 * 
	 * 获取某个时间区间整个路网的LinkTraffic记录 (暂时只考虑高等级道路)
	 * 
	 * @param startMins
	 * @param endMins
	 * @param hour
	 * @param day
	 * @param status
	 * @return
	 */
	public static List <AggLinkTraffic> getLinkTraffic(int startMins, int endMins, int startHour, int endHour, Date day, int status){
		Vector <AggLinkTraffic> linkTrafficDataSet = new Vector <AggLinkTraffic> ();
		SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-mm-dd");
		String query;
		
		if(status != -1)
			query = "SELECT * FROM fcd_cube_agg WHERE "
				+ "five_mins >= " + startMins
				+ " and five_mins <= " + endMins
				+ " and uphour >= " + startHour
				+ " and uphour <= " + endHour
				+ " and upday = " + "'" + dataFormat.format(day) + "'"
				+ " and status = " + status 
				+ " and linkid IN (Select " + "\"" + "ID" + "\"" + " FROM " + "\"" + "wuchang_Link" + "\"" + ")";
		
		else
			query = "SELECT * FROM fcd_cube_agg WHERE "
					+ "five_mins >= " + startMins
					+ " and five_mins <= " + endMins
					+ " and uphour >= " + startHour
					+ " and uphour <= " + endHour
					+ " and upday = " + "'" + dataFormat.format(day) + "'" 
					+ " and linkid IN (Select " + "\"" + "ID" + "\"" + " FROM " + "\"" + "wuchang_Link" + "\"" + ")";
			
		try{
			Connection connection = getPgConn("localhost:5432/FCD", "postgres", "123456");
			Statement select = connection.createStatement(TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet result = select.executeQuery(query);
			AggLinkTraffic linkTraffic = null;
			while(result.next()){
				linkTraffic = new AggLinkTraffic();
				linkTraffic.linkID = result.getString("linkid");
				linkTraffic.avgSpeed = result.getFloat("avg");
				linkTraffic.carNum = result.getInt("carnum");
				linkTraffic.gpsNum = result.getInt("gpsnum");
				linkTraffic.timeInterval = result.getInt("five_mins");
				linkTraffic.hour = result.getInt("uphour");
				linkTraffic.day = result.getDate("upday");
				linkTraffic.status = result.getInt("status");
				linkTrafficDataSet.add(linkTraffic);
			}
			result.close();
			select.close();
			connection.close();
		}catch(Exception e) {	        	
			System.err.println("MySQL Statement Error: " + query);
			e.printStackTrace();
		}
		
		return linkTrafficDataSet;
	}
		
	/**
	 * 
	 * 获取某道路某时间域所有记录 
	 * 
	 * @param startHour
	 * @param endHour
	 * @param day
	 * @param status
	 * @return
	 */
	public static List <AggLinkTraffic> getLinkTrafficByMLink(String mLinkID, int startHour, int endHour, Date day, int status){
		Vector <AggLinkTraffic> linkTrafficDataSet = new Vector <AggLinkTraffic> ();
		SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-mm-dd");
		String query;
		
		if(status != -1)
			query = "SELECT * FROM fcd_cube_agg WHERE "
				+ "uphour >= " + startHour
				+ " and uphour <= " + endHour
				+ " and upday = " + "'" + dataFormat.format(day) + "'"
				+ " and status = " + status
				+ " and linkid IN (Select ID FROM wuchang_llink WHERE MLINKID = " + mLinkID + ")";
		else //获取所有状态的linktraffic
			query = "SELECT * FROM fcd_cube_agg WHERE "
					+ "uphour >= " + startHour
					+ " and uphour <= " + endHour
					+ " and upday = " + "'" + dataFormat.format(day) + "'"
					+ " and linkid IN (Select ID FROM wuchang_llink WHERE MLINKID = " + mLinkID + ")";
					
		try{
			Connection connection = getPgConn("localhost:5432/FCD", "postgres", "123456");
			Statement select = connection.createStatement(TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet result = select.executeQuery(query);
			AggLinkTraffic linkTraffic = null;
			while(result.next()){
				linkTraffic = new AggLinkTraffic();
				linkTraffic.linkID = result.getString("linkid");
				linkTraffic.avgSpeed = result.getFloat("avg");
				linkTraffic.carNum = result.getInt("carnum");
				linkTraffic.gpsNum = result.getInt("gpsnum");
				linkTraffic.timeInterval = result.getInt("five_mins");
				linkTraffic.hour = result.getInt("uphour");
				linkTraffic.day = result.getDate("upday");
				linkTraffic.status = result.getInt("status");
				linkTrafficDataSet.add(linkTraffic);
			}
			result.close();
			select.close();
			connection.close();
		}catch(Exception e) {	        	
			System.err.println("MySQL Statement Error: " + query);
			e.printStackTrace();
		}
		
		return linkTrafficDataSet;
	}
	
	/**
	 * 
	 * 获取某条路段某个时间领域内的记录
	 * 
	 * @param linkID
	 * @param startTimeInterval
	 * @param endTimeInterval
	 * @param hour
	 * @param day
	 * @param status
	 * @return
	 */
	public static List <AggLinkTraffic> getLinkTrafficByLink(String linkID, int startHour, int endHour, Date day, int status){
		Vector <AggLinkTraffic> linkTrafficDataSet = new Vector <AggLinkTraffic> ();
		SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-mm-dd");
		
		String query;
		
		if(status != -1)
			 query = "SELECT * FROM fcd_cube_agg WHERE "
				+ "linkid = " + "'" + linkID + "'"
				+ " and uphour >= " + startHour
				+ " and uphour <= " + endHour
				+ " and upday = " + "'" + dataFormat.format(day) + "'" 
				+ " and status = " + status + " ORDER BY uphour, five_mins ASC";
		else
			 query = "SELECT * FROM fcd_cube_agg WHERE "
				+ "linkid = " + "'" + linkID + "'"
				+ " and uphour >= " + startHour
				+ " and uphour <= " + endHour
				+ " and upday = " + "'" + dataFormat.format(day) + "'" + " ORDER BY uphour, five_mins ASC"; 
						
		try{
			Connection connection = getPgConn("localhost:5432/FCD", "postgres", "123456");
			Statement select = connection.createStatement(TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet result = select.executeQuery(query);
			AggLinkTraffic linkTraffic = null;
			while(result.next()){
				linkTraffic = new AggLinkTraffic();
				linkTraffic.linkID = result.getString("linkid");
				linkTraffic.avgSpeed = result.getFloat("avg");
				linkTraffic.carNum = result.getInt("carnum");
				linkTraffic.gpsNum = result.getInt("gpsnum");
				linkTraffic.timeInterval = result.getInt("five_mins");
				linkTraffic.hour = result.getInt("uphour");
				linkTraffic.day = result.getDate("upday");
				linkTraffic.status = result.getInt("status");
				linkTrafficDataSet.add(linkTraffic);
			}
			connection.close();
		}catch(Exception e) {	        	
			System.err.println("MySQL Statement Error: " + query);
			e.printStackTrace();
		}
		
		return linkTrafficDataSet;
	}
	
	//----------------------------------获取RoadNetwork---------------------------------//
	
	/**
	 * 
	 * 获取路网的几何数据（通过POSTGIS访问）
	 * 
	 * 
	 * @return
	 * @throws IOException
	 */
	public static HashMap <String, Geometry> getRoadGeometrySetByDB(String tableName) throws IOException{
		HashMap <String, Geometry> map = new HashMap <String, Geometry> ();
		String query= "SELECT id, the_geom FROM " + tableName;
		String id = null;
		Geometry geom = null;
        try{
        	Connection connection = getPgConn("localhost:5432/FCD", "postgres", "123456");
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
            while(result.next()){
            	id =result.getString("id");
            	JtsGeometry jtsgeom = (JtsGeometry) result.getObject("the_geom");
            	geom = jtsgeom.getGeometry();
            	map.put(id, geom);
			}
            connection.close();
	    }catch(Exception e) {	        	
	    	System.err.println("MySQL Statement Error: " + query);
			e.printStackTrace();
	    }	
		return map;
	}
	
	public static HashMap <String, Geometry> getRoadGeometrySet(String roadShapefileURL) throws IOException {
		HashMap <String, Geometry> map = new HashMap <String, Geometry> ();
		
		FileDataStore readDataStore = FileDataStoreFinder.getDataStore(new File(roadShapefileURL));
		SimpleFeatureCollection featureCollection = readDataStore.getFeatureSource().getFeatures();
		SimpleFeatureIterator iterator = featureCollection.features();
		SimpleFeature feature = null;
		
		while(iterator.hasNext()){
			feature = iterator.next();
			map.put((String) feature.getAttribute("ID"), (Geometry)feature.getDefaultGeometry());
		}
		
		iterator.close();
		
		return map;
	}
	
	//----------------------------------仿真数据----------------------------------------//
	
	/**
	 * 获取仿真轨迹
	 * 
	 * @param carID
	 * @param fromTime
	 * @param toTime
	 * @return
	 */
	public static STrajectory getVissimTrajectoryByCID(String carID, int fromTime, int toTime){
		STrajectory traj = new STrajectory(carID, fromTime, toTime);
		
		String query= "SELECT * FROM vehicle_40s " + "WHERE carid = "
		 + "'" + carID + "'" 
		 + " AND up_time >= " 
	     + fromTime 
	     + " AND up_time <= " 
	     + toTime 
	     + " ORDER BY up_time ASC";

		
		try{
			Connection connection = getPgConn("localhost:5432/vissim", "postgres", "123456");
	        Statement select = connection.createStatement(TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	        ResultSet result = select.executeQuery(query);
	  
	        STrajectoryPoint point = null;
	        while(result.next()){
	        	point = new STrajectoryPoint();;
	            point.carID = result.getString("carid");
	            point.linkID = result.getString("linkid");
	            point.offset = result.getFloat("distance");
	            point.startSecond = result.getInt("start_time");
	            point.upSecond = result.getInt("up_time");
	            point.speed = result.getFloat("speed");
	            point.x = result.getFloat("x");
	            point.y = result.getFloat("y");
	            
	            traj.addTrajectoryPoint(point);
	        }
	        
	        result.close();
	        select.close();
	        connection.close();
		 }
		 catch(Exception e) {	        	
	        System.err.println("MySQL Statement Error: " + query);
	        e.printStackTrace();
	     }	
		
		 return traj;
	}

	//----------------------------------其它--------------------------------------------//
	private static Timestamp getTimestamp(int year, int month, int day, int seconds) {
		GregorianCalendar cal = new GregorianCalendar(year, month, day,
				seconds / 3600, seconds % 3600 / 60, seconds % 3600 % 60);
		
		return new Timestamp(cal.getTimeInMillis());
	}
	
	private static String getFileName(String filePathName) {
		String[] address = filePathName.split("\\\\");
		String[] fileName = address[address.length - 1].split("\\.");
		return fileName[0];
	}
	
	private static MathTransform getTransformFromEpsgCode(String epsgCode1, String epsgCode2) {
		CoordinateReferenceSystem sourceCRS = null;
		CoordinateReferenceSystem targetCRS = null;
		MathTransform mathTransform = null;
		try {
			sourceCRS = CRS.decode(epsgCode1, true);
			targetCRS = CRS.decode(epsgCode2, true); 
			boolean lenient = true;
			mathTransform =CRS.findMathTransform(sourceCRS, targetCRS, lenient);
		} catch (NoSuchAuthorityCodeException e) {
			e.printStackTrace();
		} catch (FactoryException e) {
			e.printStackTrace();
		} 
		return mathTransform;
	}
	
	public static void main(String[] args) throws IOException, ParseException, MismatchedDimensionException, TransformException {
/*		Trajectory tjSample = getTrajectoryByCID("11201", 25000, 40000);	
		tjSample.showHistory();*/
		
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");
		
		for(AggLinkTraffic link : getLinkTraffic(1,12, 8, 8, dateFormat.parse("2008-12-28"), 0)){
			System.out.println("LinkID: " + link.linkID + ", Hour: " + link.hour + ", 5mins: " 
		+ link.timeInterval + ", Speed: " + link.avgSpeed + ", Status: " + link.status);
			
		}
		
//		getRoadGeometrySet();
		
/*		Trajectory traj = getTrajectoryByCSVFile(new File("D:\\Lin Xu\\My Documents\\GISDataBase\\FCD\\Taxi_Li\\20081228\\11201.txt"));
	    traj.showHistory();*/
	    
	   // DBAccess.getVissimTrajectoryByCID("7629", 2000, 3200).showHistory();
	}

}
